/*
	exec sp_shipment_report_proc '0','0','0','0','0',1,'BYMONTH','XLREPORT','0'


	exec sp_shipment_report_proc '0','0','0','12/31/2007','01/27/2008',1,'BYMONTH','XLREPORT','0'

SELECT * FROM 
		 (DIST_SALES_ORDER DSO INNER JOIN DIST_LINE_ITEM_TABLE DLIT ON
	          DSO.DIST_SALESORDER_ID = DLIT.DIST_SALESORDER_ID)
*/

alter proc sp_shipment_report_proc

@PRODUCTID	VARCHAR(200),
@SONUMBER	VARCHAR(50),
@CUSTOMERID	VARCHAR(200),
@FROMDATE	VARCHAR(50),
@TODATE		VARCHAR(50),
@ORDERTYPE	INT,
@BYPERIOD	VARCHAR(50),
@REPORTTYPE	VARCHAR(20),
@DEFAULTPROMPT	VARCHAR(50)

AS
BEGIN
	CREATE TABLE #RESULTS ( PRODUCTID INT, [REQUESTED QTY] INT, [REQUESTED DATE] DATETIME, [SCHEDULED SHIP DATE] DATETIME,
				[REQUESTED DATE WORK WEEK NO] INT,[REQUESTED DATE QUARTER] INT,
				[SCHEDULE DATE WORK WEEK NO] INT, [SCHEDULE DATE QUARTER] INT,
				[PRODUCT NAME] VARCHAR(50), [ACTUAL SHIP QTY] INT,[SO NUMBER] VARCHAR(50), [CUSTOMERID] INT, 
				[CUSTOMER PO NUMBER] VARCHAR(50),[SO DATE] DATETIME, [SO WORK WEEK NO] INT,[SO QUARTER] INT, 
				SHIPVIA VARCHAR(100), [CUSTOMER NAME] VARCHAR(50),[CUSTOMER NUMBER] VARCHAR(65),[CONFIRMED DATE] datetime, 
				[ACTUAL SHIP DATE] DATETIME,[CONFIRMED DATE WORK WEEK NO] INT,[CONFIRMED DATE QUARTER] INT, [INVOICE NO]  VARCHAR(50),
				[TRACKING NUMBER] VARCHAR(150),[UNIT PRICE] FLOAT,[EXTENDED PRICE] FLOAT, 
				[END CUSTOMER] VARCHAR(65),[END USER] VARCHAR(65),
				[LINE ITEM NUMBER] INT,ISDISTRIBUTOR INT, PERIODICALVALUE INT,ACTUAL_YEAR INT,ISCONSIGNMENT BIT, [Packing Slip Label] VARCHAR(50), [REVISED SHIP DATE] DATETIME )

	DECLARE @SHIPMENTSTRING VARCHAR(4000)
	DECLARE @CREDITSTRING VARCHAR(4000)
	DECLARE @DISTRIBUTORSTRING VARCHAR(4000)

	SET @SHIPMENTSTRING = N'
	INSERT INTO #RESULTS 
	SELECT
		COOL.Product_ID,COOL.COOL_Qty,COOL.COOL_Requested_Date,COOL.COOL_Schedule_Date,	
		COOL.REQUESTED_DATE_WW_NO,REQUESTED_DATE_QTR_NO,SCHEDULE_DATE_WW_NO,SCHEDULE_DATE_QTR_NO,
		P.Part_Number,CSLIT.CSLIT_Qty_Shipped, 
		SO.SalesOrder_No,SO.Customer_ID,SO.SO_Customer_PO_Number,SO.SO_Booking_Date,
		SO.SO_BOOKING_DATE_WW_NO,SO.SO_BOOKING_DATE_QTR_NO,
		CST.CST_Method,C1.C_Name, C1.C_Number,CST_CONFIRMED_ON,
		CST.CST_Ship_Date,CST_SHIP_DATE_WW_NO,CST_SHIP_DATE_QTR_NO, Inv_No,
		CST.CST_AW_Bill_Number,COOL.COOL_Unit_Cost, 
		(COOL.COOL_Unit_Cost * CSLIT.CSLIT_Qty_Shipped ),
		(CASE 	
			WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) THEN  C_NAME 
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
 		END) ,
		(CASE 
			WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) THEN 
				(CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) THEN C_NAME 
				 	ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
			END)
		ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
		END),
		COOL.COOL_Line_Item_No,isnull(C1.C_Is_Distributor,0),
		(CASE 
			WHEN '''+ @BYPERIOD +''' = ''BYMONTH'' THEN DATEPART( m, CST.CST_CONFIRMED_ON )
			WHEN '''+ @BYPERIOD +''' = ''byQuarter'' THEN CST_SHIP_DATE_QTR_NO
			ELSE DATEPART(yyyy, CST.CST_CONFIRMED_ON )
		END),DATEPART(yyyy,CST.CST_CONFIRMED_ON),so_is_consignment, CST.Label, COOL.COOL_REVISED_DATE
--select *
	FROM
	     ((((Customer_Open_Order_LineItem COOL INNER JOIN  Customer_Shipping_Line_Item_Table CSLIT ON
	        COOL.LineItem_ID = CSLIT.LineItem_ID)
	     INNER JOIN Sales_Order SO ON COOL.SalesOrder_ID = SO.SalesOrder_ID )--and SO.SO_Is_Sample = @order_type
	     INNER JOIN Product P ON COOL.Product_ID = P.Product_ID)
	     INNER JOIN Customer_Shipping_Table CST ON  CSLIT.CustomerShipping_ID = CST.CustomerShipping_ID)
	     INNER JOIN Customer C1 ON SO.Customer_ID = C1.Customer_ID	     
	WHERE
	    CST.CST_Status = ''SHIPPED''  '

	IF @PRODUCTID IS NOT NULL AND @PRODUCTID <> '0'
	BEGIN
		SET @SHIPMENTSTRING = @SHIPMENTSTRING + ' AND COOL.Product_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCTID +'''))' 
	END
	IF @CUSTOMERID IS NOT NULL AND @CUSTOMERID <> '0'
	BEGIN
		SET @SHIPMENTSTRING = @SHIPMENTSTRING + ' AND SO.Customer_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @CUSTOMERID +'''))'
	END
	IF @SONUMBER IS NOT NULL AND @SONUMBER <> '0'
	BEGIN
		SET @SHIPMENTSTRING = @SHIPMENTSTRING + ' AND SO.SalesOrder_No = '''+ @SONUMBER+''''
	END

	IF ( @ORDERTYPE IS NOT NULL AND @ORDERTYPE  <> 2 )
	BEGIN
		SET @SHIPMENTSTRING = @SHIPMENTSTRING + ' AND so_is_consignment = 1 AND SO.SO_Is_Sample = '''+ CONVERT(VARCHAR,@ORDERTYPE)+''''
	END
	ELSE IF ( @ORDERTYPE IS NOT NULL AND @ORDERTYPE = 2 )
	BEGIN
		SET @SHIPMENTSTRING = @SHIPMENTSTRING + ' and so_is_consignment = 0 '
	END

	IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
	BEGIN
		SET @SHIPMENTSTRING = @SHIPMENTSTRING +' AND CST.CST_CONFIRMED_ON >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
	END
	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' )
	BEGIN
		SET @SHIPMENTSTRING = @SHIPMENTSTRING + +' AND CST.CST_CONFIRMED_ON <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
	END
	--print @SHIPMENTSTRING
	EXEC (@SHIPMENTSTRING)

	SET @CREDITSTRING = N'INSERT INTO #RESULTS 
		SELECT P.Product_ID, COOL.COOL_Qty AS [Requested Qty], COOL.COOL_Requested_Date, COOL.COOL_Schedule_Date, 
	COOL.REQUESTED_DATE_WW_NO,REQUESTED_DATE_QTR_NO,SCHEDULE_DATE_WW_NO,SCHEDULE_DATE_QTR_NO, P.Part_Number, 
	( CASE WHEN CNT.IS_Units = 0 THEN CNLIT.ADJUSTMENT_Qty ELSE 0 END ) AS [Actual Ship Qty],
	SO.SalesOrder_No as [SO Number], SO.Customer_ID, SO.SO_Customer_PO_Number, SO.SO_Booking_Date,
	SO.SO_BOOKING_DATE_WW_NO,SO.SO_BOOKING_DATE_QTR_NO,'''' AS [ShipVia], 
	C.C_Name as [Customer Name], C.C_Number, CNT.TIMESTAMP AS [Confirm Date], CNT.ISSUE_DATE AS [Actual Ship Date],
	CNT.CNT_APPROVED_DATE_WW_NO,CNT.CNT_APPROVED_DATE_Qtr_NO,CNT.CREDITNOTE_NO as [Invoice No], '''' AS [Tracking Number],  CNLIT.Discount_PER_UNIT AS [ Unit Price ], CNLIT.DISCOUNT_AMOUNT AS [Extended Price],
	(CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) THEN  C_NAME 
	ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID ) END) AS [End Customer] ,
	(CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) THEN 
	(CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) THEN C_NAME 
	ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )	END)
	ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)END) AS [End User],
	COOL.COOL_Line_Item_No AS [Line Item Number], ISNULL(C.C_Is_Distributor,0), 
	(CASE WHEN '''+ @BYPERIOD +''' = ''BYMONTH'' THEN DATEPART( m, CNT.TIMESTAMP )
	WHEN '''+ @BYPERIOD +''' = ''byQuarter'' THEN DATEPART( q, CNT.TIMESTAMP )
	ELSE DATEPART(yyyy, CST.CST_CONFIRMED_ON ) END),
	DATEPART(yyyy,CST.CST_CONFIRMED_ON),so_is_consignment, CST.Label, COOL.COOL_REVISED_DATE
	FROM 
	Credit_note_table CNT, Sales_Order SO, Customer C, Credit_Note_Line_Item_Table CNLIT, Customer_Open_Order_LineItem COOL,
	Customer_Shipping_Table CST, Product P WHERE 
	CNT.SalesOrder_ID = SO.SalesOrder_ID AND SO.Customer_ID = C.Customer_ID	AND CNT.CREDITNOTE_ID = CNLIT.CN_ID
	AND CNT.CustomerShipping_ID = CST.CustomerShipping_ID
	AND CNLIT.LineItem_ID = COOL.LineItem_ID
	AND COOL.Product_Id = P.Product_ID
	AND CNT.Status = ''Approved''' 
	IF @PRODUCTID IS NOT NULL AND @PRODUCTID <> '0'
	BEGIN
		SET @CREDITSTRING = @CREDITSTRING + ' AND COOL.Product_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCTID +'''))' 
	END
	IF @CUSTOMERID IS NOT NULL AND @CUSTOMERID <> '0'
	BEGIN
		SET @CREDITSTRING = @CREDITSTRING + ' AND SO.Customer_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @CUSTOMERID +'''))'
	END
	IF @SONUMBER IS NOT NULL AND @SONUMBER <> '0'
	BEGIN
		SET @CREDITSTRING = @CREDITSTRING + ' AND SO.SalesOrder_No = '''+ @SONUMBER+''''
	END

	IF ( @ORDERTYPE IS NOT NULL AND @ORDERTYPE  <> 2 )
	BEGIN
		SET @CREDITSTRING = @CREDITSTRING + ' AND so_is_consignment = 1 AND SO.SO_Is_Sample = '''+ CONVERT(VARCHAR,@ORDERTYPE)+''''
	END
	ELSE IF ( @ORDERTYPE IS NOT NULL AND @ORDERTYPE = 2 )
	BEGIN
		SET @CREDITSTRING = @CREDITSTRING + ' and so_is_consignment = 0 '
	END

	IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
	BEGIN
		SET @CREDITSTRING = @CREDITSTRING +' AND CNT.TIMESTAMP >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
	END
	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' )
	BEGIN
		SET @CREDITSTRING = @CREDITSTRING + +' AND CNT.TIMESTAMP <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
	END
	print @CREDITSTRING
	EXEC (@CREDITSTRING)

	IF @REPORTTYPE = 'XLREPORT'
	BEGIN
		select 
			[CUSTOMER NAME],[END CUSTOMER],[END USER],[SO NUMBER],[LINE ITEM NUMBER],[CUSTOMER PO NUMBER],[INVOICE NO],
			[Packing Slip Label],[SO DATE],[SO WORK WEEK NO],[SO QUARTER],[REQUESTED DATE],[REQUESTED DATE WORK WEEK NO],
			[REQUESTED DATE QUARTER],[SCHEDULED SHIP DATE],[SCHEDULE DATE WORK WEEK NO],[SCHEDULE DATE QUARTER],
		 	[REVISED SHIP DATE],[CONFIRMED DATE],[ACTUAL SHIP DATE],[CONFIRMED DATE WORK WEEK NO],[CONFIRMED DATE QUARTER], 
			[PRODUCT NAME],[REQUESTED QTY],[ACTUAL SHIP QTY],[UNIT PRICE],[EXTENDED PRICE],SHIPVIA,[TRACKING NUMBER]
		from #results
		order by ACTUAL_YEAR,PERIODICALVALUE,ISDISTRIBUTOR,[CONFIRMED DATE]
	END
	ELSE
	BEGIN
		SELECT 		
			PRODUCTID, [REQUESTED QTY], [REQUESTED DATE], [SCHEDULED SHIP DATE], 
			[PRODUCT NAME], [ACTUAL SHIP QTY],[SO NUMBER], [CUSTOMERID], 
			[CUSTOMER PO NUMBER],[SO DATE], SHIPVIA, [CUSTOMER NAME],
			[CUSTOMER NUMBER], [CONFIRMED DATE],[ACTUAL SHIP DATE], [TRACKING NUMBER],
			[UNIT PRICE],[EXTENDED PRICE], [END CUSTOMER],[END USER],[LINE ITEM NUMBER],
			ISDISTRIBUTOR, PERIODICALVALUE,ACTUAL_YEAR,ISCONSIGNMENT, [REVISED SHIP DATE]
		FROM #results
		order by ACTUAL_YEAR,PERIODICALVALUE,[CONFIRMED DATE]
	END
END